Cursor Attributes

Course- Oracle/PLSQL >

While dealing with cursors, you may need to determine the status of your cursor. The following is a list of the cursor attributes that you can use.

Attribute Explanation
%ISOPEN - Returns TRUE if the cursor is open, FALSE if the cursor is closed.
%FOUND - Returns INVALID_CURSOR if cursor is declared, but not open; or if cursor has been closed.

- Returns NULL if cursor is open, but fetch has not been executed.

- Returns TRUE if a successful fetch has been executed.

- Returns FALSE if no row was returned.

%NOTFOUND - Returns INVALID_CURSOR if cursor is declared, but not open; or if cursor has been closed.

- Return NULL if cursor is open, but fetch has not been executed.

- Returns FALSE if a successful fetch has been executed.

- Returns TRUE if no row was returned.

%ROWCOUNT - Returns INVALID_CURSOR if cursor is declared, but not open; or if cursor has been closed.

- Returns the number of rows fetched.

- The ROWCOUNT attribute doesn't give the real row count until you have iterated through the entire cursor. In other words, you shouldn't rely on this attribute to tell you how many rows are in a cursor after it is opened.

Below is an example of how you might use the %NOTFOUND attribute.

CREATE OR REPLACE Function FindCourse
   ( name_in IN varchar2 )
   RETURN number
IS
   cnumber number;

   CURSOR c1
   IS
     SELECT course_number
     FROM courses_tbl
     WHERE course_name = name_in;

BEGIN

   open c1;
   fetch c1 into cnumber;

   if c1%notfound then
      cnumber := 9999;
   end if;

   close c1;

RETURN cnumber;

END;